﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using System.Text.RegularExpressions;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace BoletoElectronicoDesktop
{


    public static class UsuarioDAO
    {
        //devuelve todos los roles habilitados
        public static DataTable FiltrarRolesHabilitados()
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_Filtro";
            command.Parameters.AddWithValue("@habilitado", "True");
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }

        //devuelve la cant de usuarios con el username ingresado
        public static int ExisteUsuario(string username)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Cantidad";
            command.Parameters.AddWithValue("@username", username);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        //da de alta un usuario
        public static void CrearUsuario(string username, string pw, string hab)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Alta";
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@password", pw);
            command.Parameters.AddWithValue("@habilitado", hab);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }

        //da de alta un rol de un usuario
        public static void InsertarRolPorUsuario(string username, string idRol)
        {
            
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Rol_Alta";
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@idRol", idRol);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            
           
        }

        //filtra los usuarios habilitados
        public static DataTable FiltrarUsuariosHabilitados(string username)
        {
      
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Filtro";
            if(string.Compare(username,"")!=0)
                command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@habilitado", "3");
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }

        //filtra los usuarios totales
        public static DataTable FiltrarUsuarios(string username)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Filtro";
            if (string.Compare(username, "") != 0)
                command.Parameters.AddWithValue("@username", username);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        //da de baja u un usuario
        public static void InhabilitarUsuario(string username)
        {
  
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Baja";
            command.Parameters.AddWithValue("@username", username);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }

        //obtiene la lista de roles de un usuario
        public static int ObtenerRolDeUsuario(string username, string idRol)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rol_delUsuario";
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@idRol", idRol);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);
        }

        //actualiza la contraseña de un usuario
        public static void ActualizarContraseña(string username, string pass)
        {
            
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_ActualizarContraseña";
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@password", pass);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }

        //habilita un usuario deshabilitado
        public static void HabilitarUsuario(string username)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_Habilitar";
            command.Parameters.AddWithValue("@username", username);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }        
        //elimina todos los roles de un usuario
        public static void EliminarRolesUsuario(string username)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Usuario_EliminarRoles";
            command.Parameters.AddWithValue("@username", username);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }
    }//fin clase


}//fin namespace